package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.common.OrderCode;
import cn.com.dashihui.web.dao.AdminSeller;
import cn.com.dashihui.web.dao.GbOrder;
import cn.com.dashihui.web.dao.Logistics;
import cn.com.dashihui.web.dao.Order;
import cn.com.dashihui.web.dao.OrderLogistics;
import cn.com.dashihui.web.dao.Refound;
import cn.com.dashihui.web.dao.SendSeller;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;

public class OrderService {

	/**
	 * 分页查找订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态，0：全部，1：待付款，2：未兑换，3：已付款，4：过期，5：删除
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPage(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,int seid,String tel,int stid,int userid){
		
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_list G "
				+ "inner JOIN t_bus_order A ON A.orderNum=G.orderNum "
				+ "LEFT JOIN t_bus_goods H ON G.goodsid=H.id "
				+ "LEFT JOIN t_bus_goods_store_rel I ON I.goodstore=H.good_store "
				+ "LEFT JOIN t_dict_store J ON J.storeid=I.storeid "
				+ "LEFT JOIN t_bus_seller C ON J.sellerId=C.sellerId "
				+ "LEFT JOIN t_auth_admin_seller K ON K.id=C.userid "
				+ "left JOIN t_bus_user F ON A.userid=F.id "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "WHERE 1=1 "
				);
		if(userid != 0){
		sBuffer.append(" AND K.id=? ");
		params.add(userid);
		}
		if(stid != 0){
			sBuffer.append(" AND J.id=? ");
			params.add(stid);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=? ");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		
		sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.orderNum,A.tel ,A.goodsName,A.payPoint,A.goodsid,A.startDate,A.countnum,A.pointType,A.amount,A.payDate,A.guoDate,"
				+ "A.deliverDate, A.orderState,A.payState,A.tuistate,A.delState,A.orderNum,A.bespokeDate,A.specTitle,J.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,C.legalPerson as jjrName,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid",sBuffer.toString(),params.toArray());
	/*	if(page.getList()!=null&&page.getList().size()!=0){
			//将各订单对应的订单商品查出
			return new Page<Record>(findGoodsByOrder(page.getList()), page.getPageNumber(), page.getPageSize(), page.getTotalPage(), page.getTotalRow());
		}*/
		return page;
	}
	/**
	 * 查询所需积分”总和，“现金价格”总和,退款价格总和，实际收入
	 */

	public Record findTotal(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,int seid,String tel,int stid,int userid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT IFNULL(ROUND(SUM(amount), 2),0.00) amount,"
				+ "IFNULL(SUM(payPoint),0) payPoint,"
				+ "IFNULL(ROUND(SUM(amount1), 2),0.00) tuikuan1,"
				+ "IFNULL(ROUND(SUM(amount2), 2),0.00) duihuan,"
				+ "IFNULL(ROUND(SUM(amount3), 2),0.00) nduihuan ,"
				+ "ROUND(IFNULL(SUM(amount3),0.00), 2) as shiji  "
				+ "from ( select case when  A.payState=2 AND (A.tuistate=2 or A.tuistate=4) then A.amount else 0 end as amount1,"
				+ "case when A.payState=2 AND A.orderState=2 then A.amount else 0.00 end as amount2,"
				+ "case when A.payState=2 AND A.tuistate!=2 AND A.tuistate!=4 then A.amount else 0.00 end as amount3,"
				+ "case when A.orderState!=5 AND A.orderState!=1 AND A.orderState!=7 AND A.orderState!=9 AND A.tuistate!=2 AND A.tuistate!=4 then A.payPoint else 0.00 end as payPoint,A.amount "
				+ "FROM t_bus_order A "
				+ "left join t_bus_user B on B.id = A.userid "
				+ "inner join t_bus_goods tg on A.goodsid = tg.id "
				+ "inner join t_bus_goods_store_rel C on C.goodstore = tg.good_store "
				+ "inner join t_dict_store D on D.storeid = C.storeid "
				+ "inner join t_bus_seller E on E.sellerId = D.sellerId "
				+ "WHERE 1=1 ");
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(seid != 0){
			sBuffer.append(" AND E.id=? ");
			params.add(seid);
		}
		if(stid != 0){
			sBuffer.append(" AND D.id=? ");
			params.add(stid);
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel like ?");
			params.add("%"+tel+"%");
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;	
	}
	/**
	 * 查询团购订单所需积分”总和，“现金价格”总和,退款价格总和，实际收入
	 */
	public Record findTotalgb(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state, String tel, String storeId,int isFree,int seid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT IFNULL(ROUND(SUM(amount), 2),0.00) amount,"
				+ "IFNULL(ROUND(SUM(amount1), 2),0.00) tuikuan1,"
				+ "ROUND(IFNULL(SUM(amount3),0.00), 2) as shiji  "
				+ "from ( select case when  A.payState=2 AND (A.tuistate=2 or A.tuistate=4) then A.amount else 0 end as amount1,"
				+ "case when A.payState=2 AND A.orderState=2 then A.amount else 0.00 end as amount2,"
				+ "case when A.payState=2 AND A.tuistate!=2 AND A.tuistate!=4 then A.amount else 0.00 end as amount3,"
				+ "case when A.orderState!=5 AND A.orderState!=1 AND A.orderState!=7 AND A.orderState!=9 AND A.tuistate!=2 AND A.tuistate!=4 then A.id else 0.00 end as iddddd,A.amount "
				+ "FROM t_bus_order_gb A "
				+ "inner join t_bus_user B on B.id = A.userid "
				+ "left join t_bus_goods tg on A.goodsid = tg.id "
				+ "inner join t_bus_goods_store_rel C on C.goodstore = tg.good_store "
				+ "inner join t_dict_store D on D.storeid = C.storeid "
				+ "inner join t_bus_seller E on E.sellerId = D.sellerId "
				+ "WHERE 1=1 ");
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		System.out.println(storeId);
		if(seid != 0){
			sBuffer.append(" AND E.id=? ");
			params.add(seid);
		}
		if(!StrKit.isBlank(storeId)){
			sBuffer.append(" AND D.storeid in ('"+storeId+"')");
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(isFree!=9){
			sBuffer.append(" AND A.isFree=? ");
			params.add(isFree);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;	
	}
	/**
	 * 分页查找团购商品订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPagegb(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,int seid,String tel,int stid,int userid,int isFree){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_gb A "
				+ "LEFT JOIN t_bus_goods H ON A.goodsid=H.id "
				+ "LEFT JOIN t_bus_goods_store_rel I ON I.goodstore=H.good_store "
				+ "LEFT JOIN t_dict_store J ON J.storeid=I.storeid "
				+ "LEFT JOIN t_bus_seller C ON J.sellerId=C.sellerId "
				+ "LEFT JOIN t_auth_admin_seller K ON K.id=C.userid "
				+ "left JOIN t_bus_user F ON A.userid=F.id "
				+ "WHERE 1=1 "
				);
		if(userid != 0){
			sBuffer.append(" AND K.id=? ");
			params.add(userid);
		}
		if(stid != 0){
			sBuffer.append(" AND J.id=? ");
			params.add(stid);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=? ");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(isFree!=9){
			sBuffer.append(" AND A.isFree=? ");
			params.add(isFree);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> pagegb = Db.paginate(pageNum,pageSize,"SELECT A.*,J.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,C.legalPerson as jjrName",sBuffer.toString(),params.toArray());
		return pagegb;
	}
	/**
	 * 兑换短信用到的，订单信息。
	 * @param OrderNum
	 * @return
	 */
	public Record orderMaessage(String OrderNum){
		return Db.findFirst("SELECT A.orderNum,A.tel ,A.goodsName ,T.name,E.title as dhstore "
				+ "FROM t_bus_order A INNER JOIN t_dict_store B ON A.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_user_wx K ON A.userid=K.id "
				+ "LEFT JOIN t_bus_user T ON A.userid=T.id "
				+ "LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid WHERE orderNum= ? " ,OrderNum);
	
	}
	/** ****************************************
	 * 用户信息
	 * @param id
	 * @return
	 */
	public User findById(int id){
		return User.me().findFirst("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount, "
				+ " (year(now())-year(A.birthday)-1) + ( DATE_FORMAT(A.birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age "
				+ " FROM t_bus_user A LEFT JOIN t_bus_user_wx B ON A.id=B.userid LEFT JOIN (SELECT userid,count(id) as countcum,sum(amount) as allCount FROM "
				+ " t_bus_order WHERE orderState=2 group by userid) C ON C.userid= B.id WHERE A.id=?",id);
	}
	/** ****************************************
	 * 用户信息
	 * @return
	 */
	public List<Store> findStoreList() {
		return Store.me().find("SELECT storeid,title FROM t_dict_store");
	}
	/******************************************
	 * 用户信息
	 * @param store
	 * @return
	 */
	public List<Record> findUserList(String store) {
		return Db.find("SELECT trueName FROM t_auth_admin_seller A LEFT JOIN t_dict_store B ON A.storeid=B.storeid WHERE B.title=?",store);
	}
	/**
	 * 从对象中获取公司sellerid
	 */
	public Record FindById(int userId) {
		return Db.findFirst("SELECT * FROM t_bus_seller WHERE userId=?",userId);
	}
	public Record findByUserId(int userid){
		return Db.findFirst("SELECT id,sellerId FROM t_bus_seller WHERE userid=?",userid);
	}
	
	/**
	 * 根据用户id查询公司ID
	 * @return
	 */
	public Record findSelleridByUserId(int userid) {
		return Db.findFirst("SELECT C.* "
				+ "FROM t_auth_admin_seller A"
				+ "LEFT JOIN t_dict_store B ON A.storeid = B.storeid"
				+ "LEFT JOIN t_bus_seller C ON B.sellerId = C.sellerId"
				+ "WHERE A.id=?",userid);
	}
	/**
	 * 查询分公司的状态是否通过
	 * @return
	 */
	public Record findStoreState(int userid) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT  * FROM  t_dict_store as A INNER JOIN t_auth_admin_seller as B ON A.storeid=B.storeid WHERE B.id=?",userid);
		
	}
	/**
	 * 分公司Id查所属公司
	 * @param storeid
	 * @return
	 */
	public Record findBysellerId(String storeid){
		return Db.findFirst("SELECT sellerId FROM t_dict_store WHERE storeid = ?",storeid);
	}
	/**
	 * 分公司Id查所属公司
	 * @param storeid
	 * @return
	 */
	public Record findBystid(int stid){
		return Db.findFirst("SELECT sellerId FROM t_dict_store WHERE id = ?",stid);
	}
	/**
	 * 为指定订单列表，查找出相应的商品清单并set给每个订单记录
	 * @param orderList
	 */
/*	private List<Record> findGoodsByOrder(List<Record> orderList){
		//拼接SQL
		StringBuffer sBuffer = new StringBuffer("SELECT orderNum,thumb,name,price,count,amount FROM t_bus_order_list WHERE orderNum IN (");
		for(Record order : orderList){
			sBuffer.append("'").append(order.getStr("orderNum")).append("',");
		}
		sBuffer.replace(sBuffer.lastIndexOf(","), sBuffer.length(), ")");
		sBuffer.append(" ORDER BY orderNum");
		//整理结果
		List<Record> goodsList = Db.find(sBuffer.toString());
		if(goodsList!=null){
			//先将查出来的所有的订单商品，按订单号归类
			Map<String,List<Record>> goodsMap = new HashMap<String,List<Record>>();
			for(Record goods : goodsList){
				if(goodsMap.containsKey(goods.getStr("orderNum"))){
					((List<Record>)goodsMap.get(goods.getStr("orderNum"))).add(goods);
				}else{
					goodsMap.put(goods.getStr("orderNum"), new ListKit<Record>().add(goods).getList());
				}
			}
			//遍历订单列表的同时，设置对应订单商品集
			for(Record order : orderList){
				order.set("goodsList", goodsMap.get(order.get("orderNum")));
			}
		}
		return orderList;
	}*/

	/**
	 * 导出订单数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "订单统计列表", titles, listz);
	}
	public void exportdi(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "积分抵现列表", titles, listz);
	}
	public List<Record> findExcelByPage(String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("SELECT A.orderNum,A.tel ,A.goodsName,A.payPoint,A.goodsid,A.startDate,A.countnum,A.pointType,A.amount,A.payDate,A.guoDate,"
				+ "A.deliverDate, A.orderState,A.payState,A.tuistate,A.delState,A.orderNum,A.bespokeDate,A.specTitle,B.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,E.title as dhstore,C.legalPerson as jjrName,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid FROM t_bus_order A "
				+ "inner JOIN t_bus_user F ON A.userid=F.id "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C ON F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user_wx D ON A.userid=D.id "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
			sBuffer.append(" AND C.sellerId=?");
			params.add(sellerId);
		
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid=?");
			params.add(storeid);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1://待付款（
			sBuffer.append(" AND payState=? AND delState=?");
			params.add(OrderCode.OrderPayState.NO_PAY);
			params.add(OrderCode.DelState.NO);
			break;
		case 2://未兑换
			sBuffer.append(" AND payState=? AND orderState=?  AND tuistate=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.NO_CON);
			params.add(OrderCode.RefundState.ZERO);
			break;
		case 3://已付款
			sBuffer.append(" AND payState=? AND orderState=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.had_CON);
			break;
		case 4://过期
			sBuffer.append(" AND payState=? AND orderState=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.EXPIRE);
			break;
		case 5://删除
			sBuffer.append(" AND payState=? AND delState=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.DelState.YES);
			break;
		case 6://取消
			sBuffer.append(" AND payState=? AND delState=?");
			params.add(OrderCode.OrderPayState.NO_PAY);
			params.add(OrderCode.DelState.YES);
			break;
		case 7://退款中
			sBuffer.append(" AND payState=? AND orderState=? AND tuistate=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.NO_CON);
			params.add(OrderCode.RefundState.VERIFYING);
			break;
		case 8://已退款
			sBuffer.append(" AND payState=? AND orderState=? AND tuistate=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.NO_CON);
			params.add(OrderCode.RefundState.REFUNDED);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		sBuffer.append(" ORDER BY A.createDate DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	/**
	 * 导出团购订单数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void exportgb(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "团购订单统计列表", titles, listz);
	}
	public void exportdigb(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "积分抵现列表", titles, listz);
	}
	public List<Record> findExcelByPagegb(String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid){
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,B.title as xdstore,T.name,E.title as dhstore FROM t_bus_order_gb A LEFT JOIN t_dict_store B ON A.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ " LEFT JOIN t_bus_user_wx K ON A.userid=K.id "
				+ "LEFT JOIN t_bus_user T ON A.userid=T.id "
				+ "LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid WHERE 1=1");
		List<Object> params = new ArrayList<Object>();
	
			sBuffer.append(" AND C.sellerId=?");
			params.add(sellerId);
		
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid=?");
			params.add(storeid);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
			case 1://待付款（
				sBuffer.append(" AND payState=? ");
				params.add(OrderCode.OrderPayState.NO_PAY);
				break;
			case 2://未兑换
				sBuffer.append(" AND payState=? AND orderState=? AND tuistate=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.NO_CON);
				params.add(OrderCode.RefundState.ZERO);
				break;
			case 3://已付款
				sBuffer.append(" AND payState=? AND orderState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.had_CON);
				break;
			case 4://过期
				sBuffer.append(" AND payState=? AND orderState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.EXPIRE);
				break;
			case 5://删除
				sBuffer.append(" AND payState=? AND delState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.DelState.YES);
				break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		sBuffer.append(" ORDER BY A.createDate DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	/**
	 * 查询兑换码的状态
	 */
	public Order getOrderByOrder(String duihuanma){
		String sql = "SELECT A.*, B.title AS xdtitel , E.title AS dhtitel FROM t_bus_order A INNER JOIN t_dict_store B ON A.storeid = B.storeid "
				+ " LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid WHERE  A.duihuanma=?";
		return Order.me().findFirst(sql,duihuanma);
	}
	/**
	 * 查询买家电话
	 */
	public String getByPhone(String orderNum){
		String sql = "SELECT tel FROM t_bus_order WHERE orderNum=? ";
		return Db.queryStr(sql,orderNum);
	}
	/**
	 * 查询平台电话
	 */
	public List<Record> getBySellerPhone(int type){
		String sql = "SELECT phone FROM t_sys_sms WHERE type=1 OR type = ? ";
		return Db.find(sql,type);
	}
	/**
	 * 查询公司电话
	 */
	public List<Record> getBySellPhone(int sellerid){
		String sql = "SELECT phone FROM t_sys_sms WHERE type=4 AND sellerid=? ";
		return Db.find(sql,sellerid);
	}
	/**
	 * 查询分公司电话
	 */
	public List<Record> getByStorePhone(int storeid){
		String sql = "SELECT phone FROM t_sys_sms WHERE type=5 AND storeid=? ";
		return Db.find(sql,storeid);
	}
	/**
	 * 查询商品的类型：常规，团购。
	 */
	public Record FingFtype(String orderNum){
		String sql = "SELECT B.ftype FROM t_bus_order A INNER JOIN t_bus_goods B ON A.goodsid=B.goodsid WHERE orderNum= ? ";
		return Db.findFirst(sql,orderNum);
	}
	
	
	/**
	 * 查询该分公司是否是兑换分公司。
	 */
	public String getOrderByid(String duihuanma,String storeid){
		String sql = "SELECT orderNum FROM t_bus_order WHERE duihuanma=? AND storeid=? ";
		return Db.queryStr(sql,duihuanma,storeid);
	}
	/**
	 * 查询当前的分公司，兑换操作人所在的分公司
	 */
	public  Record findStoreTitle(int userid) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT  * FROM  t_dict_store as A INNER JOIN t_auth_admin_seller as B ON A.storeid=B.storeid WHERE B.id=?",userid);
		
	}
	/**
	 *修改商品
	 */
	public boolean updateOrder(Order order){
		return order.update();
	}
	
	
	/**
	 * 根据订单号查询出订单对象
	 */
	public Order getOrderByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order WHERE orderNum=?";
		return Order.me().findFirst(sql,orderNum);
	}
	/**
	 * 根据订单号查询出订单配送地址
	 */
	public Order findOrderByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order A "
				+ " left join t_bus_user_address B on B.id = A.addressid "
				+ " WHERE A.orderNum=?";
		return Order.me().findFirst(sql,orderNum);
	}
	/**
	 * 根据订单号查询出订单配送地址
	 */
	public Order findOrderByOrderNumgb(String orderNum){
		String sql = "SELECT * FROM t_bus_order_gb A "
				+ " left join t_bus_user_address B on B.id = A.addressid "
				+ " WHERE A.orderNum=?";
		return Order.me().findFirst(sql,orderNum);
	}
	/**
	 * 根据订单号查询出商品清单
	 */
	public List<Record> getGoodsListByOrderNum(String orderNum){
		String sql = "SELECT A.orderNum,A.goodsid,A.name as title,A.price,A.count,A.amount,B.name FROM t_bus_order_list A left join t_bus_goods B on A.goodsid = B.id WHERE A.orderNum=?";
		return Db.find(sql, orderNum);
	}
	/**
	 * 根据订单号查询出团购商品清单
	 */
	public List<Record> getGoodsListByOrderNumgb(String orderNum){
		String sql = "SELECT B.* FROM t_bus_order_gb A left join t_bus_goods B on A.goodsid = B.id WHERE A.orderNum=?";
		return Db.find(sql, orderNum);
	}
	/**
	 * 根据订单号查询订单操作日志
	 */
	public List<Record> getLogListByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order_log WHERE orderNum=? ORDER BY createDate";
		return Db.find(sql, orderNum);
	}
	/**
	 * 根据订单号查询团购订单操作日志
	 */
	public List<Record> getLogListByOrderNumgb(String orderNum){
		String sql = "SELECT * FROM t_bus_order_log WHERE orderNum=? ORDER BY createDate";
		return Db.find(sql, orderNum);
	}
	/**
	 * 根据订单号查询订单评价信息
	 */
	public Record getEvalByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order_eval WHERE orderNum=?";
		return Db.findFirst(sql, orderNum);
	}
	/**
	 * 更新
	 */
	public boolean update(Order order){
		return order.update();
	}
	/**
	 * 将list<object[]>集合转换为object[][]
	 * @param params list<object[]>集合
	 */
/*	private Object[][] parseParams(List<Object[]> params){
		if(params!=null){
			Object[][] result = new Object[params.size()][];
			for(int index = 0;index<params.size();index++){
				result[index] = params.get(index);
			}
			return result;
		}
		return null;
	}
	*/
	/**
	 * 记录操作日志
	 */
	public void log(String orderNum, int type, String user, String action, String content){
		Db.update("INSERT INTO t_bus_order_log(orderNum,type,user,action,content) VALUES(?,?,?,?,?)",orderNum,type,user,action,content);
	}
	
	public Record findStoreSellerId(String dhstoreid) {
		return Db.findFirst("SELECT B.id,C.id as sellerid  FROM  t_dict_store B LEFT JOIN t_bus_seller C ON B.sellerId=C.sellerId WHERE B.storeid= ?",dhstoreid);		
	}
	public Record findStore(int userid) {
		return Db.findFirst("SELECT B.dixian,B.point,B.storeid FROM t_auth_admin_seller A LEFT JOIN t_dict_store B ON A.storeid=B.storeid WHERE A.id=?",userid);
	}
	public Record findUserByPhone(String phone) {
		return Db.findFirst("SELECT name,sppoint,jkpoint,jypoint FROM t_bus_user WHERE phone=?",phone);
	}
	public Page<Record> findBydixianPage(int pageNum, int pageSize, String toBossOneId, String beginDate, String endDate,
			String sSellerId, String phone, String storeId) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("FROM t_dict_store_dixian A INNER JOIN t_dict_store B ON A.storeid = B.storeid INNER JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_user T ON A.phone=T.phone LEFT JOIN t_dict_store E ON A.storeid = E.storeid  WHERE 1=1 ");
			sBuffer.append(" AND C.sellerId=?");
			params.add(sSellerId);
		if(!StrKit.isBlank(storeId)&&!"0".equals(storeId)){
			sBuffer.append(" AND A.storeid in ('"+storeId+"')");
		}
		if(!StrKit.isBlank(toBossOneId)){
			sBuffer.append(" AND T.toBossOneId=?");
			params.add(toBossOneId);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(!StrKit.isBlank(phone)){
			sBuffer.append(" AND A.phone=?");
			params.add(phone);
		}
		sBuffer.append(" ORDER BY A.date DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,T.name,T.store,T.toBossOneId,E.title ",sBuffer.toString(),params.toArray());
		return page;
	}
	public List<Record> findExcelByPage(String toBossOneId, String beginDate, String endDate, String sSellerId, String phone,String storeId) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT A.*,T.name,B.title as store ,E.title FROM t_dict_store_dixian A INNER JOIN t_dict_store B ON A.storeid = B.storeid INNER JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_user T ON B.id=T.toBossTwoId LEFT JOIN t_dict_store E ON A.storeid = E.storeid  WHERE 1=1 ");
		
			sBuffer.append(" AND C.sellerId=?");
			params.add(sSellerId);
	
		if(!StrKit.isBlank(storeId)&&!"0".equals(storeId)){
			sBuffer.append(" AND A.storeid in ('"+storeId+"')");
		}
		if(!StrKit.isBlank(toBossOneId)){
			sBuffer.append(" AND T.toBossOneId=?");
			params.add(toBossOneId);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(!StrKit.isBlank(phone)){
			sBuffer.append(" AND A.phone=?");
			params.add(phone);
		}
		sBuffer.append(" ORDER BY A.date DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	public User findByUser(String phone) {
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE phone=?",phone);
	}
	/**
	 * 分页查找当前用户的所有订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findUserOrder(int pageNum,int pageSize,int userId){
			String sql=" FROM t_bus_order A "
					+ " LEFT JOIN t_bus_user_wx B ON B.id=A.userid "
					+ " LEFT JOIN t_bus_user C ON C.id=A.userid"
					+ " LEFT JOIN t_dict_store D ON D.storeid=A.storeid "
					+ " LEFT JOIN t_dict_store E ON E.storeid=A.dhstoreid "
					+ "LEFT JOIN t_bus_seller F ON C.toBossThreeId=F.id "
					+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
					+ " WHERE A.payState=2 AND C.id="+userId +" ORDER BY A.startDate DESC";
			return Db.paginate(pageNum, pageSize,
					"SELECT A.*,C.*,D.title xdstore,F.legalPerson as jjrName,E.title dhstore,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid ",sql);
	}
	/**
	 * 根据用户id导出订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public List<Record> findUserOrder(int userId){	
			return Db.find("SELECT A.*,C.*,D.title xdstore,E.title dhstore "
					+  "FROM t_bus_order A LEFT JOIN t_bus_user_wx B ON B.id=A.userid "
					+ " LEFT JOIN t_bus_user C ON C.id=B.userid LEFT JOIN t_dict_store D ON D.storeid=A.storeid "
					+ " LEFT JOIN t_dict_store E ON E.storeid=A.dhstoreid  WHERE A.orderState=2 AND C.id="+userId +" ORDER BY A.startDate DESC");
	}
	public Record findToker(int tokerid) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT * FROM t_bus_order_toker_user_log WHERE id=?",tokerid);
	}
	public User findUser(int id) {
		// TODO Auto-generated method stub
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE id=?",id);
	}
	/** ****************************************
	 *获取所有的快递公司
	 * @return
	 */
	public List<Logistics> findAllLogistics() {
		return Logistics.me().find("SELECT * FROM t_dict_logistics");
	}
	/**
	 *查询所有的sendSeller
	 */
	public List<SendSeller> findSendSeller(){
		return SendSeller.me().find("SELECT * FROM t_dict_sendseller");
	}
	/**
	 * 查询当前保存的订单物流信息的订单
	 * @param id
	 * @return
	 */
	public Order findByOrderNum(String orderNum) {
		// TODO Auto-generated method stub
		return Order.me().findFirst("SELECT * FROM t_bus_order WHERE orderNum=?",orderNum);
	}
	/**
	 * 查询当前保存的订单物流信息的团购订单
	 * @param id
	 * @return
	 */
	public GbOrder findByOrderNumgb(String orderNum) {
		// TODO Auto-generated method stub
		return GbOrder.me().findFirst("SELECT * FROM t_bus_order_gb WHERE orderNum=?",orderNum);
	}
	/**
	 * 查询当前运单是否存在，一个运单不可以存在多张订单
	 * @param id
	 * @return
	 */
	public boolean findExistsByLogisticCode(String logisticCode) {
		// TODO Auto-generated method stub
		return OrderLogistics.me().findFirst("SELECT * FROM t_bus_order_logistics WHERE logisticCode=?",logisticCode)!=null;
	}
	/**
	 * 查询当前订单是否存在，一个顶单不可以多次发货
	 * @param id
	 * @return
	 */
	public boolean findExistsByOrderNum(String orderNum) {
		// TODO Auto-generated method stub
		return OrderLogistics.me().findFirst("SELECT * FROM t_bus_order_logistics WHERE orderCode=?",orderNum)!=null;
	}

	/**
	 * 查询当前保存的订单物流信息的订单
	 * @param id
	 * @return
	 */
	public OrderLogistics findWBByOrderNum(String orderNum) {
		// TODO Auto-generated method stub
		return OrderLogistics.me().findFirst("SELECT * FROM t_bus_order_logistics WHERE orderCode=?",orderNum);
	}
	/**
	 * 查询退款单
	 * @param id
	 * @return
	 */
	public Refound findRefound(String orderNum) {
		// TODO Auto-generated method stub
		return Refound.me().findFirst("SELECT * FROM t_bus_order_refund WHERE orderNum=?",orderNum);
	}

	public AdminSeller findAdseller(int id){
		return AdminSeller.me().findById(id);
	}
	
}
